------------------------------------------------
-- Export file for user OPS                   --
-- Created by 138962 on 12/4/2014, 2:55:26 PM --
------------------------------------------------

spool Procs.log

prompt
prompt Creating function OPS_COUNT_PARAMS
prompt ==================================
prompt
create or replace function OPS_COUNT_PARAMS(p_parameters IN VARCHAR2) return number is
  Result number;
begin
  Result := regexp_count(p_parameters, ':');
  return(Result);
end OPS_COUNT_PARAMS;
/

prompt
prompt Creating function OPS_DATE_FORMAT
prompt =================================
prompt
create or replace function OPS_DATE_FORMAT return varchar2 is
  Result varchar2(20);
  I_TEST_VAL varchar2(30);
begin
  BEGIN
       SELECT LOOKUP_VALUE_NAME
       INTO Result FROM OPS_LOOKUPS
       WHERE LOOKUP_TYPE = 'DATE-FORMAT'
       AND LOOKUP_VALUE_KEY = 'DFORMAT'
       AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/YYYY'))
       AND DELETED_BY IS NULL;
       SELECT TO_CHAR(SYSDATE, Result) INTO I_TEST_VAL FROM DUAL;
  EXCEPTION
           WHEN OTHERS THEN
      Result := 'DD/MM/YYYY';
  END;
  return(Result);
end OPS_DATE_FORMAT;
/

prompt
prompt Creating function OPS_DATE_TIME_FORMAT
prompt ======================================
prompt
create or replace function OPS_DATE_TIME_FORMAT return varchar2 is
  Result varchar2(30);
  I_TEST_VAL varchar2(30);
begin
  BEGIN
       SELECT LOOKUP_VALUE_NAME
       INTO Result FROM OPS_LOOKUPS
       WHERE LOOKUP_TYPE = 'DATE-TIME-FORMAT'
       AND LOOKUP_VALUE_KEY = 'DTFORMAT'
       AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/YYYY'))
       AND DELETED_BY IS NULL;
       SELECT TO_CHAR(SYSDATE, Result) INTO I_TEST_VAL FROM DUAL;
  EXCEPTION
           WHEN OTHERS THEN
      Result := 'DD/MM/YYYY HH24:MI:SS';
  END;
  return(Result);
end OPS_DATE_TIME_FORMAT;
/

prompt
prompt Creating function OPS_GET_PARAMS
prompt ================================
prompt
create or replace function OPS_GET_PARAMS(p_parameters IN VARCHAR2, p_index number) return varchar2 is
  Result varchar2(100);
begin
  Result := replace(p_parameters, '=' ,'');
  Result := replace(Result, '(' ,'');
  Result := replace(Result, ')' ,'');
  Result := REGEXP_SUBSTR(Result, '[^:]+', 1, p_index);
  Result := REGEXP_SUBSTR(Result, '[^ ]+', 1, 1);
  return(Result);
end OPS_GET_PARAMS;
/

prompt
prompt Creating function OPS_GET_SESSION_ID
prompt ====================================
prompt
CREATE OR REPLACE FUNCTION OPS_GET_SESSION_ID(p_user_session IN VARCHAR2, p_user_id IN VARCHAR2) return number is
  I_USER_SESSSION number;
BEGIN
  BEGIN
    SELECT
    S.USER_SESSION_ID
    INTO
    I_USER_SESSSION
    FROM
    OPS_USER_SESSIONS S
    WHERE
    S.USER_ID = P_USER_ID
    AND S.LOGOUT_TIME IS NULL
    AND S.CREATED_SESSION = p_user_session;
  EXCEPTION WHEN OTHERS THEN
         I_USER_SESSSION := NULL;
  END;
  RETURN I_USER_SESSSION;

END OPS_GET_SESSION_ID;
/

prompt
prompt Creating function OPS_MAX_DATE
prompt ==============================
prompt
create or replace function OPS_MAX_DATE return varchar2 is
  Result varchar2(20);
  I_TEST_VAL varchar2(30);
begin
  BEGIN
       SELECT LOOKUP_VALUE_NAME
       INTO Result FROM OPS_LOOKUPS
       WHERE LOOKUP_TYPE = 'OPS-MAX-DATE'
       AND LOOKUP_VALUE_KEY = 'MAXDATE'
       AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/YYYY'))
       AND DELETED_BY IS NULL;
       SELECT TO_DATE(Result, OPS_DATE_FORMAT()) INTO I_TEST_VAL FROM DUAL;
  EXCEPTION
           WHEN OTHERS THEN
      Result := '01/01/9999';
  END;
  return(Result);
end OPS_MAX_DATE;
/

prompt
prompt Creating procedure SP_LOGIN
prompt ===========================
prompt
create or replace procedure SP_LOGIN
(
p_user_id 	IN 	number, -- in the final script this need to maped to column
p_password 	IN 	varchar2,
p_session_id 	IN 	varchar2 Default null,
p_user		OUT 	SYS_REFCURSOR,
p_roles         OUT 	SYS_REFCURSOR,
p_menu          OUT 	SYS_REFCURSOR,
pov_error_code   OUT 	Varchar2,
pov_error_msg  OUT 	Varchar2
)
AS
/**********************************

***********************************/
USER_CNT NUMBER;
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);

BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

SELECT COUNT(1) INTO USER_CNT FROM OPS_USERS U WHERE U.USER_ID = p_user_id AND U.USER_PASSWORD = p_password AND U.USER_STATUS = 'A' AND U.DELETED_DATE IS NULL;

IF (USER_CNT != 1) THEN
    pov_error_code := 'LG-001';
    pov_error_msg := 'Invalid user/password';
ELSE
    SELECT
    COUNT(1) into USER_CNT
    FROM
    OPS_ROLES R,OPS_USER_ROLES UR
    WHERE
    R.Role_id = UR.ROLE_ID
    AND UR.User_id = p_user_id
    AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN R.Start_Date_Active AND NVL(R.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT));

    IF (USER_CNT = 0) THEN
      pov_error_code := 'LG-002';
      pov_error_msg := 'No role exists for the user';
    ELSE

      select
      COUNT(1) INTO USER_CNT
      from
      OPS_MENUS M,OPS_ROLE_MENUS MR, OPS_USER_ROLES UR
      WHERE
      M.Menu_id = MR.Menu_id
      AND UR.Role_id = MR.Role_id
      AND UR.User_id = p_user_id
      AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
      AND SYSDATE BETWEEN M.Start_Date_Active AND NVL(M.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
      AND SYSDATE BETWEEN MR.Start_Date_Active AND NVL(MR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT));

      IF (USER_CNT = 0) THEN
        pov_error_code := 'LG-003';
        pov_error_msg := 'No menu assigned to the role for the user';
      END IF;


    END IF;
END IF;




    OPEN p_user   FOR
    SELECT
    U.USER_ID,
    U.USER_NAME,
    U.USER_STATUS,
    U.CREATED_BY,
    TO_CHAR(U.CREATED_DATE, P_OPS_DATE_FORMAT) CREATED_DATE,
    U.CREATED_SESSION,
    U.UPDATED_BY,
    TO_CHAR(U.UPDATED_DATE, P_OPS_DATE_FORMAT) UPDATED_DATE,
    U.UPDATED_SESSION,
    U.DELETED_BY,
    TO_CHAR(U.DELETED_DATE, P_OPS_DATE_FORMAT) DELETED_DATE,
    U.DELETED_SESSION,
    U.USER_PASSWORD
    FROM
    OPS_USERS U WHERE
    U.USER_ID = p_user_id AND U.USER_PASSWORD = p_password AND U.USER_STATUS = 'A' AND U.DELETED_DATE IS NULL;

    OPEN p_roles FOR
    SELECT
    R.ROLE_ID,
    R.ROLE_NAME,
    'A' ROLE_STATUS,
    R.Create_By CREATED_BY,
    TO_CHAR(R.Create_Date, P_OPS_DATE_FORMAT) CREATED_DATE,
    R.Create_Session CREATED_SESSION,
    R.Update_By UPDATED_BY,
    TO_CHAR(R.Update_Date, P_OPS_DATE_FORMAT) UPDATED_DATE,
    R.Update_session UPDATED_SESSION,
    R.Delete_By DELETED_BY,
    TO_CHAR(R.Delete_Date, P_OPS_DATE_FORMAT) DELETED_DATE,
    R.Delete_Session DELETED_SESSION
    FROM
    OPS_ROLES R,OPS_USER_ROLES UR
    WHERE
    R.Role_id = UR.ROLE_ID
    AND UR.User_id = p_user_id
    AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN R.Start_Date_Active AND NVL(R.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT));


    open P_MENU for
    select
    M.Menu_id menuId,
     M.Menu_Name menuName,
    'A' menuStatus,
     M.Parent_Menu_Id parentMenuId,
     M.MENU_CLASS menuClass,
     M.MENU_DIVISION menuDivision,
     M.MENU_URL menuURL,
     M.Create_By createdBy,
    TO_CHAR( M.Create_Date, P_OPS_DATE_FORMAT) createdDate,
     M.Create_Session createdSession,
     M.Update_By updatedBy,
    TO_CHAR( M.Update_Date, P_OPS_DATE_FORMAT) updatedDate,
     M.Update_Session updatedSession,
     M.Delete_By deletedBy,
    TO_CHAR( M.Delete_Date, P_OPS_DATE_FORMAT) deletedDate,
     M.Delete_Session deletedSession
    from
    OPS_MENUS M,OPS_ROLE_MENUS MR, OPS_USER_ROLES UR
    WHERE
    M.Menu_id = MR.Menu_id
    AND UR.Role_id = MR.Role_id
    AND UR.User_id = p_user_id
    AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN M.Start_Date_Active AND NVL(M.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN MR.Start_Date_Active AND NVL(MR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT));

    IF (pov_error_code = '0000') THEN

      BEGIN
        INSERT INTO OPS_USER_SESSIONS
        (
          USER_SESSION_ID,
          USER_ID,
          LOGIN_TIME,
          LOGOUT_TIME,
          CREATED_BY,
          CREATED_DATE,
          CREATED_SESSION,
          UPDATED_BY,
          UPDATED_DATE,
          UPDATED_SESSION,
          DELETED_BY,
          DELETED_DATE,
          DELETED_SESSION
        )
        VALUES
        (
           OPS_USER_SESSIONS_SEQ.NEXTVAL,
           p_user_id,
           SYSDATE,
           NULL,
           p_user_id,
           SYSDATE,
           p_session_id,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL
        );
      EXCEPTION
               WHEN OTHERS THEN
                  pov_error_code := SQLCODE;
                  pov_error_msg := SQLERRM;

      END;
    END IF;

END;
/

prompt
prompt Creating procedure SP_LOGOUT
prompt ============================
prompt
create or replace procedure SP_LOGOUT
(
p_user_id 	IN 	number, -- in the final script this need to maped to column
p_session_id 	IN 	varchar2 Default null,
pov_error_code   OUT 	Varchar2,
pov_error_msg  OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_session_id, p_user_id);

    UPDATE OPS_USER_SESSIONS
    SET LOGOUT_TIME = SYSDATE,
        UPDATED_BY = p_user_id,
        UPDATED_DATE = SYSDATE,
        UPDATED_SESSION = p_session_id
    WHERE
        USER_ID =  p_user_id
        AND LOGOUT_TIME IS NULL
        AND CREATED_SESSION = p_session_id;


END;
/

prompt
prompt Creating procedure SP_RECORD_USER_ACTION
prompt ========================================
prompt
create or replace procedure SP_RECORD_USER_ACTION
(
p_user_action 	IN 	varchar2, -- in the final script this need to maped to column
p_object_type 	IN 	varchar2,
p_object_id 	IN 	varchar2,
p_user_id		IN 	varchar2,
p_user_session		IN 	varchar2,
pov_error_code   OUT 	Varchar2,
pov_error_msg  OUT 	Varchar2
)
AS
/**********************************

***********************************/

I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

    BEGIN

        INSERT INTO OPS_USER_ACTIONS
        (
         USER_ACTION_ID,
         USER_SESSION_ID,
         ACTION,
         OBJECT_TYPE,
         OBJECT_ID,
         ACTION_DATE,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_USER_ACTIONS_SEQ.NEXTVAL,
         I_SESSION_ID,
         p_user_action,
         p_object_type,
         p_object_id,
         SYSDATE,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
    EXCEPTION
             WHEN OTHERS THEN
                pov_error_code := SQLCODE;
                pov_error_msg := SQLERRM;

    END;

END;
/

prompt
prompt Creating procedure SP_RETRIEVE_MENUS
prompt ====================================
prompt
create or replace procedure SP_RETRIEVE_MENUS
(
p_user_id 	IN 	number, -- in the final script this need to maped to column
p_role_id 	IN 	varchar2,
p_menus          OUT 	SYS_REFCURSOR,
pov_error_code   OUT 	Varchar2,
pov_error_msg  OUT 	Varchar2
)
AS
/**********************************

***********************************/
USER_CNT NUMBER;
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

    open p_menus for
    select
    M.Menu_id menuId,
     M.Menu_Name menuName,
    'A' menuStatus,
     M.Parent_Menu_Id parentMenuId,
     M.MENU_CLASS menuClass,
     M.MENU_DIVISION menuDivision,
     M.MENU_URL menuURL,
     M.Create_By createdBy,
    TO_CHAR( M.Create_Date, P_OPS_DATE_FORMAT) createdDate,
     M.Create_Session createdSession,
     M.Update_By updatedBy,
    TO_CHAR( M.Update_Date, P_OPS_DATE_FORMAT) updatedDate,
     M.Update_Session updatedSession,
     M.Delete_By deletedBy,
    TO_CHAR( M.Delete_Date, P_OPS_DATE_FORMAT) deletedDate,
     M.Delete_Session deletedSession
    from
    OPS_MENUS M,OPS_ROLE_MENUS MR, OPS_USER_ROLES UR
    WHERE
    M.Menu_id = MR.Menu_id
    AND UR.Role_id = MR.Role_id
    AND UR.User_id = p_user_id
    AND UR.Role_id = p_role_id
    AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN M.Start_Date_Active AND NVL(M.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN MR.Start_Date_Active AND NVL(MR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT));

END;
/

prompt
prompt Creating procedure SP_SEARCH_LOOKUPS
prompt ====================================
prompt
create or replace procedure SP_SEARCH_LOOKUPS
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_type 	IN 	varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_lookups         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_lookups   FOR
    SELECT
    LOOKUP_ID,
		LOOKUP_TYPE,
    LOOKUP_VALUE_NAME,
    LOOKUP_VALUE_KEY,
		LOOKUP_VALUE_DESCRIPTION,
    ASSOCIATED_RULE,
		to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
		to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_LOOKUPS
    WHERE
    (LOOKUP_VALUE_NAME like p_name || '%' OR p_name IS NULL)
    AND ( LOOKUP_TYPE = p_type OR p_type IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETED_BY IS NULL
     ORDER BY LOOKUP_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_MESSAGES
prompt =====================================
prompt
create or replace procedure SP_SEARCH_MESSAGES
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_lang 	IN 	varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_messages         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_messages   FOR
    SELECT
    MESSAGE_ID,
		MESSAGE_LANG,
    MESSAGE_KEY,
    MESSAGE_NAME,
		PARAMETER_VALUE,
		to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
		to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_MESSAGES
    WHERE
    (MESSAGE_NAME like p_name || '%' OR p_name IS NULL)
    AND ( MESSAGE_LANG = p_lang OR p_lang IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETED_BY IS NULL;



END;
/

prompt
prompt Creating procedure SP_SEARCH_PARAMETERS
prompt =======================================
prompt
create or replace procedure SP_SEARCH_PARAMETERS
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_type 	IN 	varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_parameters         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_parameters   FOR
    SELECT
    PARAMETER_ID,
		PARAMETER_TYPE,
    PARAMETER_KEY,
		PARAMETER_NAME,
		PARAMETER_VALUE,
		to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
		to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_PARAMETERS
    WHERE
    (PARAMETER_NAME LIKE p_name || '%' OR p_name IS NULL)
    AND ( PARAMETER_TYPE = p_type OR p_type IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETED_BY IS NULL;



END;
/

prompt
prompt Creating procedure SP_SEARCH_RATES
prompt ==================================
prompt
create or replace procedure SP_SEARCH_RATES
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_taxAuth 	IN 	varchar2,
p_taxType 	IN 	varchar2,
p_rateType 	IN 	varchar2,
p_rates         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();

OPEN p_rates   FOR
    SELECT
    RATE_ID,
		RANGE_RULE_NAME,
    TAX_AUTHORITY,
    TAX_TYPE,
		RATE_TYPE,
    PARENT_RATE_ID,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_RATES
    WHERE
    (RANGE_RULE_NAME like p_name || '%' OR p_name IS NULL)
    AND (TAX_AUTHORITY = p_taxAuth OR p_taxAuth IS NULL)
    AND (TAX_TYPE = p_taxType OR p_taxType IS NULL)
    AND (RATE_TYPE = p_rateType OR p_rateType IS NULL)
    AND DELETED_BY IS NULL
    ORDER BY RATE_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_ROLES
prompt ==================================
prompt
create or replace procedure SP_SEARCH_ROLES
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_active_from 	IN 	DATE,
p_active_to 	IN 	DATE,
p_roles         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_roles   FOR
    SELECT
    ROLE_ID,
    ROLE_NAME,
    to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
    to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATE_BY,
		to_char(CREATE_DATE,P_OPS_DATE_FORMAT) CREATE_DATE,
		CREATE_SESSION,
		UPDATE_BY,
		to_char(UPDATE_DATE,P_OPS_DATE_FORMAT) UPDATE_DATE,
		UPDATE_SESSION,
		DELETE_BY,
		to_char(DELETE_DATE,P_OPS_DATE_FORMAT) DELETE_DATE,
		DELETE_SESSION
    FROM
    OPS_ROLES
    WHERE
    (ROLE_NAME like p_name || '%' OR p_name IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETE_BY IS NULL
    ORDER BY ROLE_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_RULES
prompt ==================================
prompt
create or replace procedure SP_SEARCH_RULES
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_type 	IN 	varchar2,
p_group 	IN 	varchar2,
p_datatype 	IN 	varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_rules         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_rules   FOR
    SELECT
    RULE_ID,
		RULE_GROUP,
    RULE_NAME,
    RULE_DESCRIPTION,
		RULE_TYPE,
    RULE_DATATYPE,
    CALC_TYPE,
    RULE_FORMULA,
    RULE_PARAMS,
    RULE_FILTER,
    RULE_RATE,
    RULE_LEVEL,
    VALUE_FORMAT,
    IS_REQUIRED,
		to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
		to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_RULES
    WHERE
    (RULE_NAME like p_name || '%' OR p_name IS NULL)
    AND ( RULE_TYPE = p_type OR p_type IS NULL)
    AND ( RULE_GROUP = p_group OR p_group IS NULL)
    AND ( RULE_DATATYPE = p_datatype OR p_datatype IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETED_BY IS NULL
     ORDER BY RULE_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_RULE_BINDS
prompt =======================================
prompt
create or replace procedure SP_SEARCH_RULE_BINDS
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_bindVar 	IN 	varchar2,
p_bindType 	IN 	varchar2,
p_ruleLevel 	IN 	varchar2,
p_binds         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();

OPEN p_binds   FOR
    SELECT
    B.RULEBIND_ID,
    B.RULE_ID,
    B.BIND_VAR,
    B.BIND_TYPE,
    (SELECT R1.RULE_NAME FROM OPS_RULES R1 WHERE R1.RULE_ID = B.BIND_VALUE) BIND_VALUE,
    B.RULE_LEVEL,
		B.CREATED_BY,
		to_char(B.CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		B.CREATED_SESSION,
		B.UPDATED_BY,
		to_char(B.UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		B.UPDATED_SESSION,
		B.DELETED_BY,
		to_char(B.DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		B.DELETED_SESSION
    FROM
    OPS_RULE_BINDS B, OPS_RULES R
    WHERE
    (R.RULE_NAME like p_name || '%' OR p_name IS NULL)
    AND R.RULE_ID = B.RULE_ID
    AND (B.BIND_VAR = p_bindVar OR p_bindVar IS NULL)
    AND (B.BIND_TYPE = p_bindType OR p_bindType IS NULL)
    AND (B.RULE_LEVEL = p_ruleLevel OR p_ruleLevel IS NULL)
    AND B.DELETED_BY IS NULL
    AND R.DELETED_BY IS NULL
    ORDER BY RULEBIND_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_TAX_AUTHORITY
prompt ==========================================
prompt
create or replace procedure SP_SEARCH_TAX_AUTHORITY
(
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_tax_type 	IN 	varchar2,
p_rate_type 	IN 	varchar2,
p_rule_group 	IN 	varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_tax_authority         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_tax_authority   FOR
    SELECT
    TAXAUTHORITY_ID,
    TAX_AUTHORITY,
    TAX_TYPE,
    RATE_TYPE,
    ADDRESS1,
    ADDRESS2,
    CITY,
    STATE,
    COUNTRY,
    ZIPCODE,
    RULE_GROUP,
		to_char(START_DATE_ACTIVE,P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
		to_char(END_DATE_ACTIVE,P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
		CREATED_BY,
		to_char(CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		CREATED_SESSION_ID,
		UPDATED_BY,
		to_char(UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UPDATED_SESSION,
		DELETED_BY,
		to_char(DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		DELETED_SESSION
    FROM
    OPS_TAX_AUTHORITIES
    WHERE
    (TAX_AUTHORITY LIKE p_name || '%' OR p_name IS NULL)
    AND ( TAX_TYPE = p_tax_type OR p_tax_type IS NULL)
    AND ( RATE_TYPE = p_rate_type OR p_rate_type IS NULL)
    AND ( RULE_GROUP = p_rule_group OR p_rule_group IS NULL)
    AND
     (
     (p_active_from IS NULL AND p_active_to IS NULL)
     OR
     (START_DATE_ACTIVE >= p_active_from AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)) <= NVL(p_active_to,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT)))
     )
     AND DELETED_BY IS NULL;

END;
/

prompt
prompt Creating procedure SP_SEARCH_USERS
prompt ==================================
prompt
create or replace procedure SP_SEARCH_USERS
(
p_user_name 	IN 	varchar2, -- in the final script this need to maped to column
p_status 	IN 	varchar2 Default null,
p_users		OUT 	SYS_REFCURSOR,
p_roles   OUT   SYS_REFCURSOR,
pov_error_code   OUT 	Varchar2,
pov_error_msg  OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();



    OPEN p_users   FOR
    SELECT
    U.USER_ID,
    U.USER_NAME,
    U.USER_STATUS,
    U.CREATED_BY,
    TO_CHAR(U.CREATED_DATE, P_OPS_DATE_FORMAT) CREATED_DATE,
    U.CREATED_SESSION,
    U.UPDATED_BY,
    TO_CHAR(U.UPDATED_DATE, P_OPS_DATE_FORMAT) UPDATED_DATE,
    U.UPDATED_SESSION,
    U.DELETED_BY,
    TO_CHAR(U.DELETED_DATE, P_OPS_DATE_FORMAT) DELETED_DATE,
    U.DELETED_SESSION,
    U.USER_PASSWORD
    FROM
    OPS_USERS U WHERE
    (U.USER_NAME like p_user_name || '%' or p_user_name IS NULL)
    AND (U.USER_STATUS = p_status or p_status is null)
    AND U.DELETED_DATE IS NULL;

    OPEN p_roles FOR
    SELECT
    UR.USER_ID,
    R.ROLE_ID,
    TO_CHAR(UR.START_DATE_ACTIVE, P_OPS_DATE_FORMAT) START_DATE_ACTIVE,
    TO_CHAR(UR.END_DATE_ACTIVE, P_OPS_DATE_FORMAT) END_DATE_ACTIVE,
    R.Create_By CREATED_BY,
    TO_CHAR(R.Create_Date, P_OPS_DATE_FORMAT) CREATED_DATE,
    R.Create_Session CREATED_SESSION,
    R.Update_By UPDATED_BY,
    TO_CHAR(R.Update_Date, P_OPS_DATE_FORMAT) UPDATED_DATE,
    R.Update_session UPDATED_SESSION,
    R.Delete_By DELETED_BY,
    TO_CHAR(R.Delete_Date, P_OPS_DATE_FORMAT) DELETED_DATE,
    R.Delete_Session DELETED_SESSION
    FROM
    OPS_ROLES R,OPS_USER_ROLES UR, OPS_USERS U
    WHERE
    (U.USER_NAME like p_user_name || '%' or p_user_name IS NULL)
    AND (U.USER_STATUS = p_status or p_status is null)
    AND U.DELETED_DATE IS NULL
    AND R.Role_id = UR.ROLE_ID
    AND UR.User_id = U.USER_ID
    AND SYSDATE BETWEEN UR.Start_Date_Active AND NVL(UR.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    AND SYSDATE BETWEEN R.Start_Date_Active AND NVL(R.End_Date_Active,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
    ORDER BY UR.User_id;

END;
/

prompt
prompt Creating procedure SP_SEARCH_USER_ACTIONS
prompt =========================================
prompt
create or replace procedure SP_SEARCH_USER_ACTIONS
(
p_action 	IN 	varchar2, -- in the final script this need to maped to column
p_user 	IN 	varchar2,
p_object_type 	IN 	varchar2,
p_action_from		IN 	DATE,
p_action_to		IN 	DATE,
p_user_actions         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_DATE_TIME_FORMAT VARCHAR2(30);
P_OPS_MAX_DATE VARCHAR2(20);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';

P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_DATE_TIME_FORMAT := OPS_DATE_TIME_FORMAT();
P_OPS_MAX_DATE := OPS_MAX_DATE();

OPEN p_user_actions   FOR
    SELECT
    UA.USER_ACTION_ID,
    U.USER_NAME USER_SESSION_ID,
    UA.ACTION,
    UA.OBJECT_TYPE,
    UA.OBJECT_ID,
    to_char(UA.ACTION_DATE, P_OPS_DATE_TIME_FORMAT) ACTION_DATE,
		UA.CREATED_BY,
		to_char(UA.CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		UA.CREATED_SESSION,
		UA.UPDATED_BY,
		to_char(UA.UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		UA.UPDATED_SESSION,
		UA.DELETED_BY,
		to_char(UA.DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		UA.DELETED_SESSION
    FROM
    OPS_USER_ACTIONS UA, OPS_USERS U, OPS_USER_SESSIONS US
    WHERE
    US.USER_SESSION_ID = UA.USER_SESSION_ID
    AND U.USER_ID = US.USER_ID
    AND (U.USER_ID = p_user OR p_user IS NULL)
    AND (UA.ACTION = p_action OR p_action IS NULL)
    AND ( UA.OBJECT_TYPE = p_object_type OR p_object_type IS NULL)
    AND
     (
     (p_action_from IS NULL AND p_action_to IS NULL)
     OR
     (UA.ACTION_DATE BETWEEN p_action_from AND NVL(p_action_to, TO_DATE(P_OPS_MAX_DATE, P_OPS_DATE_FORMAT)))
     )
     AND UA.DELETED_BY IS NULL
     ORDER BY UA.USER_ACTION_ID;

END;
/

prompt
prompt Creating procedure SP_SEARCH_USER_SESSIONS
prompt ==========================================
prompt
create or replace procedure SP_SEARCH_USER_SESSIONS
(
p_user 	IN 	varchar2, -- in the final script this need to maped to column
p_session_start_time 	IN 	DATE,
p_session_end_time		IN 	DATE,
p_user_sessions         OUT 	SYS_REFCURSOR,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
/**********************************

***********************************/
P_OPS_DATE_FORMAT VARCHAR2(20);
P_OPS_DATE_TIME_FORMAT VARCHAR2(30);
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
P_OPS_DATE_TIME_FORMAT := OPS_DATE_TIME_FORMAT();

OPEN p_user_sessions   FOR
    SELECT
    OS.USER_SESSION_ID,
    S.USER_NAME USER_ID,
    TO_CHAR(OS.LOGIN_TIME, P_OPS_DATE_TIME_FORMAT) LOGIN_TIME,
    TO_CHAR(OS.LOGOUT_TIME, P_OPS_DATE_TIME_FORMAT) LOGOUT_TIME,
 		OS.CREATED_BY,
		to_char(OS.CREATED_DATE,P_OPS_DATE_FORMAT) CREATED_DATE,
		OS.CREATED_SESSION,
		OS.UPDATED_BY,
		to_char(OS.UPDATED_DATE,P_OPS_DATE_FORMAT) UPDATED_DATE,
		OS.UPDATED_SESSION,
		OS.DELETED_BY,
		to_char(OS.DELETED_DATE,P_OPS_DATE_FORMAT) DELETED_DATE,
		OS.DELETED_SESSION
    FROM
    OPS_USER_SESSIONS OS, OPS_USERS S
    WHERE
    (OS.USER_ID = p_user OR p_user IS NULL)
    AND (OS.LOGIN_TIME >= p_session_start_time or p_session_start_time IS NULL)
    AND (OS.LOGIN_TIME <= p_session_end_time or p_session_end_time IS NULL)
    AND OS.DELETED_BY IS NULL
    AND S.USER_ID = OS.USER_ID
    AND S.USER_STATUS = 'A'
    AND S.DELETED_DATE IS NULL;

END;
/

prompt
prompt Creating procedure SP_UPDATE_LOOKUPS
prompt ====================================
prompt
create or replace procedure SP_UPDATE_LOOKUPS
(
p_id 	IN 	number,
p_type 	IN 	varchar2,
p_name 	IN 	varchar2,
p_key 	IN 	varchar2,
p_value 	IN 	varchar2,
p_rule 	IN 	varchar2,
p_active_from 	IN 	DATE Default null,
p_active_to		IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);


BEGIN
IF(p_id IS NULL) THEN
        INSERT INTO OPS_LOOKUPS
        (
         LOOKUP_ID,
         LOOKUP_TYPE,
         LOOKUP_VALUE_NAME,
         LOOKUP_VALUE_KEY,
         LOOKUP_VALUE_DESCRIPTION,
         ASSOCIATED_RULE,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_LOOKUPS_SEQ.NEXTVAL,
         p_type,
         p_name,
         p_key,
         p_value,
         p_rule,
         p_active_from,
         p_active_to,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE
  UPDATE
  OPS_LOOKUPS
  SET
  LOOKUP_TYPE = decode(p_operation_flag,'U',p_type, 'D', LOOKUP_TYPE),
  LOOKUP_VALUE_NAME = decode(p_operation_flag,'U',p_name, 'D', LOOKUP_VALUE_NAME),
  LOOKUP_VALUE_KEY = decode(p_operation_flag,'U',p_key, 'D', LOOKUP_VALUE_KEY),
  LOOKUP_VALUE_DESCRIPTION = decode(p_operation_flag,'U',p_value, 'D', LOOKUP_VALUE_DESCRIPTION),
  ASSOCIATED_RULE = decode(p_operation_flag,'U',p_rule, 'D', ASSOCIATED_RULE),
  START_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_from, 'D', START_DATE_ACTIVE),
  END_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_to, 'D', END_DATE_ACTIVE),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', p_user_session)
  WHERE
  LOOKUP_ID = p_id;
END IF;
EXCEPTION
          WHEN OTHERS THEN
          pov_error_code := SQLCODE;
          pov_error_msg := SQLERRM;

END;
END;
/

prompt
prompt Creating procedure SP_UPDATE_MESSAGES
prompt =====================================
prompt
create or replace procedure SP_UPDATE_MESSAGES
(
p_id 	IN 	number,
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_lang 	IN 	varchar2,
p_key 	IN 	varchar2,
p_value 	IN 	varchar2,
p_active_from 	IN 	DATE Default null,
p_active_to		IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);
BEGIN
IF(p_id IS NULL) THEN
        INSERT INTO OPS_MESSAGES
        (
         MESSAGE_ID,
         MESSAGE_LANG,
         MESSAGE_KEY,
         MESSAGE_NAME,
         PARAMETER_VALUE,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_MESSAGES_SEQ.NEXTVAL,
         p_lang,
         p_key,
         p_name,
         p_value,
         p_active_from,
         p_active_to,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE
  UPDATE
  OPS_MESSAGES
  SET
  MESSAGE_LANG = decode(p_operation_flag,'U',p_lang, 'D', MESSAGE_LANG),
  MESSAGE_KEY = decode(p_operation_flag,'U',p_key, 'D', MESSAGE_KEY),
  MESSAGE_NAME = decode(p_operation_flag,'U',p_name, 'D', MESSAGE_NAME),
  PARAMETER_VALUE = decode(p_operation_flag,'U',p_value, 'D', PARAMETER_VALUE),
  START_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_from, 'D', START_DATE_ACTIVE),
  END_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_to, 'D', END_DATE_ACTIVE),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
  WHERE
  MESSAGE_ID = p_id;
END IF;
EXCEPTION
          WHEN OTHERS THEN
          pov_error_code := SQLCODE;
          pov_error_msg := SQLERRM;

END;
END;
/

prompt
prompt Creating procedure SP_UPDATE_PARAMETERS
prompt =======================================
prompt
create or replace procedure SP_UPDATE_PARAMETERS
(
p_id 	IN 	number,
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_type 	IN 	varchar2,
p_value 	IN 	varchar2,
p_key 	IN 	varchar2,
p_active_from 	IN 	DATE Default null,
p_active_to		IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

IF(p_id IS NULL) THEN
        INSERT INTO OPS_PARAMETERS
        (
         PARAMETER_ID,
         PARAMETER_TYPE,
         PARAMETER_KEY,
         PARAMETER_NAME,
         PARAMETER_VALUE,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_PARAMETERS_SEQ.NEXTVAL,
         p_type,
         p_key,
         p_name,
         p_value,
         p_active_from,
         p_active_to,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE
  UPDATE
  OPS_PARAMETERS
  SET
  PARAMETER_TYPE = decode(p_operation_flag,'U',p_type, 'D', PARAMETER_TYPE),
  PARAMETER_KEY = decode(p_operation_flag,'U',p_key, 'D', PARAMETER_KEY),
  PARAMETER_NAME = decode(p_operation_flag,'U',p_name, 'D', PARAMETER_NAME),
  PARAMETER_VALUE = decode(p_operation_flag,'U',p_value, 'D', PARAMETER_VALUE),
  START_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_from, 'D', START_DATE_ACTIVE),
  END_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_to, 'D', END_DATE_ACTIVE),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
  WHERE
  PARAMETER_ID = p_id;
END IF;

END;
/

prompt
prompt Creating procedure SP_UPDATE_RATES
prompt ==================================
prompt
create or replace procedure SP_UPDATE_RATES
(
p_id 	IN 	number,
p_name 	IN 	varchar2,
p_taxAuth 	IN 	varchar2,
p_tax 	IN 	varchar2,
p_type 	IN 	varchar2,
p_parent 	IN 	varchar2,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_RATE_PARENT_CNT NUMBER;
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

BEGIN
IF(p_id IS NULL) THEN
        INSERT INTO OPS_RATES
        (
         RATE_ID,
         RANGE_RULE_NAME,
         TAX_AUTHORITY,
         TAX_TYPE,
         RATE_TYPE,
         PARENT_RATE_ID,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_RATES_SEQ.NEXTVAL,
         p_name,
         p_taxAuth,
         p_tax,
         p_type,
         p_parent,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE

  IF (p_operation_flag ='D') THEN
    SELECT COUNT(1) INTO I_RATE_PARENT_CNT FROM OPS_RATES WHERE PARENT_RATE_ID = p_id AND DELETED_DATE IS NULL;
    IF(I_RATE_PARENT_CNT > 0) THEN
          pov_error_code := 'RA01';
          pov_error_msg := 'The Rate can not be deleted as its been used in other rate defination';
          RETURN;
    END IF;
  END IF;

  UPDATE
  OPS_RATES
  SET
  RANGE_RULE_NAME = decode(p_operation_flag,'U',p_name, 'D', RANGE_RULE_NAME),
  TAX_AUTHORITY = decode(p_operation_flag,'U',p_taxAuth, 'D', TAX_AUTHORITY),
  TAX_TYPE = decode(p_operation_flag,'U',p_tax, 'D', TAX_TYPE),
  RATE_TYPE = decode(p_operation_flag,'U',p_type, 'D', RATE_TYPE),
  PARENT_RATE_ID = decode(p_operation_flag,'U',p_parent, 'D', PARENT_RATE_ID),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
  WHERE
  RATE_ID = p_id;
END IF;
EXCEPTION
          WHEN OTHERS THEN
          pov_error_code := SQLCODE;
          pov_error_msg := SQLERRM;

END;
END;
/

prompt
prompt Creating procedure SP_UPDATE_RULES
prompt ==================================
prompt
create or replace procedure SP_UPDATE_RULES
(
p_id 	IN 	number,
p_group 	IN 	varchar2,
p_name 	IN 	varchar2,
p_description 	IN 	varchar2,
p_ruleType 	IN 	varchar2,
p_dataType 	IN 	varchar2,
p_calcType 	IN 	varchar2,
p_formula 	IN 	varchar2,
p_parameters 	IN 	varchar2,
p_filter 	IN 	varchar2,
p_rate 	IN 	varchar2,
p_level 	IN 	varchar2,
p_valueFormat 	IN 	varchar2,
p_required 	IN 	varchar2,
p_active_from 	IN 	DATE Default null,
p_active_to		IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
  AS
  I_PARAM_CNT NUMBER;
  I_PARAM VARCHAR2(100);
  I_RULE_ID NUMBER;

  P_OPS_DATE_FORMAT VARCHAR2(20);
  P_OPS_MAX_DATE VARCHAR2(20);

  I_PARAM_RULE_ID NUMBER;
  I_SESSION_ID NUMBER;
  BEGIN

  pov_error_code := '0000';
  pov_error_msg := 'SUCCESS';
  I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);
  P_OPS_DATE_FORMAT := OPS_DATE_FORMAT();
  P_OPS_MAX_DATE := OPS_MAX_DATE();



  IF(p_id IS NULL) THEN

          SELECT OPS_RULES_SEQ.NEXTVAL INTO I_RULE_ID FROM DUAL;


          IF (NVL(p_ruleType,'X') <> 'F') THEN

              I_PARAM_CNT := OPS_COUNT_PARAMS(p_parameters || ' ' || p_filter);
              IF (I_PARAM_CNT > 0) THEN
                FOR ICNT IN 1..I_PARAM_CNT LOOP
                   I_PARAM := OPS_GET_PARAMS(p_parameters || ' ' || p_filter, ICNT);

                   BEGIN
                   SELECT
                   RULE_ID
                   INTO
                   I_PARAM_RULE_ID
                   FROM
                   OPS_RULES
                   WHERE
                   RULE_NAME = UPPER(I_PARAM)
                   AND RULE_LEVEL = p_level
                   AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
                   AND DELETED_BY IS NULL;


                      INSERT INTO
                      OPS_RULE_BINDS
                      (
                       RULEBIND_ID,
                       RULE_ID,
                       BIND_VAR,
                       BIND_TYPE,
                       BIND_VALUE,
                       RULE_LEVEL,
                       CREATED_BY,
                       CREATED_DATE,
                       CREATED_SESSION,
                       UPDATED_BY,
                       UPDATED_DATE,
                       UPDATED_SESSION,
                       DELETED_BY,
                       DELETED_DATE,
                       DELETED_SESSION
                      )
                      VALUES
                      (
                         OPS_RULE_BINDS_SEQ.NEXTVAL,
                         I_RULE_ID,
                         UPPER(I_PARAM),
                         'RULE',
                         I_PARAM_RULE_ID,
                         p_level,
                         p_user_id,
                         SYSDATE,
                         I_SESSION_ID,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL
                      );

                   EXCEPTION
                            WHEN NO_DATA_FOUND THEN
                            pov_error_code := 'RL01';
                            pov_error_msg := 'Parameter ' || I_PARAM || ' rule does not exists';
                            RETURN;
                   END;

                END LOOP;
              END IF;
          END IF;

          INSERT INTO OPS_RULES
          (
           RULE_ID,
           RULE_GROUP,
           RULE_NAME,
           RULE_DESCRIPTION,
           RULE_TYPE,
           RULE_DATATYPE,
           CALC_TYPE,
           RULE_FORMULA,
           RULE_PARAMS,
           RULE_FILTER,
           RULE_RATE,
           RULE_LEVEL,
           VALUE_FORMAT,
           IS_REQUIRED,
           START_DATE_ACTIVE,
           END_DATE_ACTIVE,
           CREATED_BY,
           CREATED_DATE,
           CREATED_SESSION,
           UPDATED_BY,
           UPDATED_DATE,
           UPDATED_SESSION,
           DELETED_BY,
           DELETED_DATE,
           DELETED_SESSION
          )
          VALUES
          (
           I_RULE_ID,
           p_group,
           p_name,
           p_description,
           p_ruleType,
           p_dataType,
           p_calcType,
           p_formula,
           p_parameters,
           p_filter,
           p_rate,
           p_level,
           p_valueFormat,
           p_required,
           p_active_from,
           p_active_to,
           p_user_id,
           SYSDATE,
           I_SESSION_ID,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL
          );
  ELSE
          DELETE FROM OPS_RULE_BINDS WHERE RULE_ID = p_id;

          IF (NVL(p_ruleType,'X') <> 'F') THEN

              I_PARAM_CNT := OPS_COUNT_PARAMS(p_parameters || ' ' || p_filter);
              IF (I_PARAM_CNT > 0) THEN
                FOR ICNT IN 1..I_PARAM_CNT LOOP
                   I_PARAM := OPS_GET_PARAMS(p_parameters || ' ' || p_filter, ICNT);

                   BEGIN
                   SELECT
                   RULE_ID
                   INTO
                   I_PARAM_RULE_ID
                   FROM
                   OPS_RULES
                   WHERE
                   RULE_NAME = UPPER(I_PARAM)
                   AND RULE_LEVEL = p_level
                   AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,TO_DATE(P_OPS_MAX_DATE,P_OPS_DATE_FORMAT))
                   AND DELETED_BY IS NULL;


                      INSERT INTO
                      OPS_RULE_BINDS
                      (
                       RULEBIND_ID,
                       RULE_ID,
                       BIND_VAR,
                       BIND_TYPE,
                       BIND_VALUE,
                       RULE_LEVEL,
                       CREATED_BY,
                       CREATED_DATE,
                       CREATED_SESSION,
                       UPDATED_BY,
                       UPDATED_DATE,
                       UPDATED_SESSION,
                       DELETED_BY,
                       DELETED_DATE,
                       DELETED_SESSION
                      )
                      VALUES
                      (
                         OPS_RULE_BINDS_SEQ.NEXTVAL,
                         p_id,
                         UPPER(I_PARAM),
                         'RULE',
                         I_PARAM_RULE_ID,
                         p_level,
                         p_user_id,
                         SYSDATE,
                         I_SESSION_ID,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL
                      );

                   EXCEPTION
                            WHEN NO_DATA_FOUND THEN
                            pov_error_code := 'RL01';
                            pov_error_msg := 'Parameter ' || I_PARAM || ' rule does not exists';
                            RETURN;
                   END;

                END LOOP;
              END IF;
          END IF;



          UPDATE
          OPS_RULES
          SET
          RULE_GROUP = decode(p_operation_flag,'U',p_group, 'D', RULE_GROUP),
          RULE_NAME = decode(p_operation_flag,'U',p_name, 'D', RULE_NAME),
          RULE_DESCRIPTION = decode(p_operation_flag,'U',p_description, 'D', RULE_DESCRIPTION),
          RULE_TYPE = decode(p_operation_flag,'U',p_ruleType, 'D', RULE_TYPE),
          RULE_DATATYPE = decode(p_operation_flag,'U',p_dataType, 'D', RULE_DATATYPE),
          CALC_TYPE = decode(p_operation_flag,'U',p_calcType, 'D', CALC_TYPE),
          RULE_FORMULA = decode(p_operation_flag,'U',p_formula, 'D', RULE_FORMULA),
          RULE_PARAMS = decode(p_operation_flag,'U',p_parameters, 'D', RULE_PARAMS),
          RULE_FILTER = decode(p_operation_flag,'U',p_filter, 'D', RULE_FILTER),
          RULE_RATE = decode(p_operation_flag,'U',p_rate, 'D', RULE_RATE),
          RULE_LEVEL = decode(p_operation_flag,'U',p_level, 'D', RULE_LEVEL),
          VALUE_FORMAT = decode(p_operation_flag,'U',p_valueFormat, 'D', VALUE_FORMAT),
          IS_REQUIRED = decode(p_operation_flag,'U',p_required, 'D', IS_REQUIRED),
          START_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_from, 'D', START_DATE_ACTIVE),
          END_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_to, 'D', END_DATE_ACTIVE),
          UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
          UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
          UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
          DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
          DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
          DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
          WHERE
          RULE_ID = p_id;
  END IF;
  EXCEPTION
            WHEN OTHERS THEN
            pov_error_code := SQLCODE;
            pov_error_msg := SQLERRM;
END;
/

prompt
prompt Creating procedure SP_UPDATE_TAX_AUTHORITY
prompt ==========================================
prompt
create or replace procedure SP_UPDATE_TAX_AUTHORITY
(
p_id 	IN 	number,
p_name 	IN 	varchar2, -- in the final script this need to maped to column
p_tax_type 	IN 	varchar2,
p_rate_type 	IN 	varchar2,
p_address1 	IN 	varchar2,
p_address2 	IN 	varchar2,
p_city 	IN 	varchar2,
p_state 	IN 	varchar2,
p_country 	IN 	varchar2,
p_zip_code 	IN 	varchar2,
p_rule_group IN varchar2,
p_active_from 	IN 	DATE,
p_active_to		IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

IF(p_id IS NULL) THEN
        INSERT INTO OPS_TAX_AUTHORITIES
        (
         TAXAUTHORITY_ID,
         TAX_AUTHORITY,
         TAX_TYPE,
         RATE_TYPE,
         ADDRESS1,
         ADDRESS2,
         CITY,
         STATE,
         COUNTRY,
         ZIPCODE,
         RULE_GROUP,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION_ID,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         OPS_TAX_AUTHORITY_SEQ.NEXTVAL,
         p_name,
         p_tax_type,
         p_rate_type,
         p_address1,
         p_address2,
         p_city,
         p_state,
         p_country,
         p_zip_code,
         p_rule_group,
         p_active_from,
         p_active_to,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE
  UPDATE
  OPS_TAX_AUTHORITIES
  SET
  TAX_AUTHORITY = decode(p_operation_flag,'U',p_name, 'D', TAX_AUTHORITY),
  TAX_TYPE = decode(p_operation_flag,'U',p_tax_type, 'D', TAX_TYPE),
  RATE_TYPE = decode(p_operation_flag,'U',p_rate_type, 'D', RATE_TYPE),
  ADDRESS1 = decode(p_operation_flag,'U',p_address1, 'D', ADDRESS1),
  ADDRESS2 = decode(p_operation_flag,'U',p_address2, 'D', ADDRESS2),
  CITY = decode(p_operation_flag,'U',p_city, 'D', CITY),
  STATE = decode(p_operation_flag,'U',p_state, 'D', STATE),
  COUNTRY = decode(p_operation_flag,'U',p_country, 'D', COUNTRY),
  ZIPCODE = decode(p_operation_flag,'U',p_zip_code, 'D', ZIPCODE),
  RULE_GROUP = decode(p_operation_flag,'U',p_rule_group, 'D', RULE_GROUP),
  START_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_from, 'D', START_DATE_ACTIVE),
  END_DATE_ACTIVE = decode(p_operation_flag,'U',p_active_to, 'D', END_DATE_ACTIVE),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
  WHERE
  TAXAUTHORITY_ID = p_id;
END IF;

END;
/

prompt
prompt Creating procedure SP_UPDATE_USERS
prompt ==================================
prompt
create or replace procedure SP_UPDATE_USERS
(
p_id 	IN 	number,
p_name 	IN 	varchar2,
p_status 	IN 	varchar2,
p_password 	IN 	varchar2,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
p_operation_flag IN varchar2,
po_user_id OUT varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

IF(p_id IS NULL) THEN
        po_user_id := OPS_USERS_SEQ.NEXTVAL;
        INSERT INTO OPS_USERS
        (
         USER_ID,
         USER_NAME,
         USER_STATUS,
         USER_PASSWORD,
         CREATED_BY,
         CREATED_DATE,
         CREATED_SESSION,
         UPDATED_BY,
         UPDATED_DATE,
         UPDATED_SESSION,
         DELETED_BY,
         DELETED_DATE,
         DELETED_SESSION
        )
        VALUES
        (
         po_user_id,
         p_name,
         p_status,
         p_password,
         p_user_id,
         SYSDATE,
         I_SESSION_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
        );
ELSE
  po_user_id := p_id;
  UPDATE
  OPS_USERS
  SET
  USER_NAME = decode(p_operation_flag,'U',p_name, 'D', USER_NAME),
  USER_STATUS = decode(p_operation_flag,'U',p_status, 'D', USER_STATUS),
  --USER_PASSWORD = decode(p_operation_flag,'U',p_password, 'D', USER_PASSWORD),
  UPDATED_BY = decode(p_operation_flag,'U',p_user_id, 'D', UPDATED_BY),
  UPDATED_DATE = decode(p_operation_flag,'U',SYSDATE, 'D', UPDATED_DATE),
  UPDATED_SESSION = decode(p_operation_flag,'U',I_SESSION_ID, 'D', UPDATED_SESSION),
  DELETED_BY = decode(p_operation_flag,'U',DELETED_BY, 'D', p_user_id),
  DELETED_DATE = decode(p_operation_flag,'U',DELETED_DATE, 'D', SYSDATE),
  DELETED_SESSION = decode(p_operation_flag,'U',DELETED_SESSION, 'D', I_SESSION_ID)
  WHERE
  USER_ID = po_user_id;
END IF;

DELETE FROM OPS_USER_ROLES WHERE USER_ID = po_user_id;

END;
/

prompt
prompt Creating procedure SP_UPDATE_USER_ROLES
prompt =======================================
prompt
create or replace procedure SP_UPDATE_USER_ROLES
(
p_u_id 	IN 	number,
p_role_id 	IN 	number,
p_active_from 	IN 	DATE,
p_active_to 	IN 	DATE,
p_user_id IN 	varchar2,
p_user_session IN varchar2,
pov_error_code  OUT 	Varchar2,
pov_error_msg   OUT 	Varchar2
)
AS
I_SESSION_ID NUMBER;
URS_ROLE_CNT NUMBER;
BEGIN

pov_error_code := '0000';
pov_error_msg := 'SUCCESS';
I_SESSION_ID := OPS_GET_SESSION_ID(p_user_session, p_user_id);

  SELECT COUNT(1) INTO URS_ROLE_CNT
  FROM
  OPS_USER_ROLES
  WHERE
  USER_ID = p_u_id
  AND ROLE_ID = p_role_id;
  
  IF(URS_ROLE_CNT = 0) THEN
  
    INSERT INTO OPS_USER_ROLES
    (
    USER_ROLE_ID,
    USER_ID,
    ROLE_ID,
    START_DATE_ACTIVE,
    END_DATE_ACTIVE,
    CREATE_BY,
    CREATE_DATE,
    CREATE_SESSION,
    UPDATE_BY,
    UPDATE_DATE,
    UPDATE_SESSION,
    DELETE_BY,
    DELETE_DATE,
    DELETE_SESSION
    )
    VALUES
    (
    OPS_USERS_ROLE_SEQ.NEXTVAL,
    p_u_id,
    p_role_id,
    p_active_from,
    p_active_to,
    p_user_id,
    SYSDATE,
    I_SESSION_ID,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
    );
  ELSE
      pov_error_code := 'UR01';
      pov_error_msg := 'Role already exists for the user';
  END IF;

END;
/


spool off
